﻿using CRL;
using System;
using System.Linq;

namespace QueryTest.test
{
    public class group
    {
        #region group
        public static void testGroup()
        {
            #region GROUP
            var query = Code.ProductDataRepository.Instance.GetLambdaQuery();
            query.Page(1,5);
            //GROUP条件
            var group = query.Where(b => b.Id > 0).GroupBy(b => new { b.ProductName });

            //having
            group.GroupHaving(b => b.Number.SUM() >= 0);
            //设置排序
            group.OrderBy(b => b.BarCode.Count(), true);//等效为 order by count(BarCode) desc

            //选择GROUP字段
            var list4 = group.Select(b => new
            {
                sum2 = b.SUM(x => x.Number * x.Id),//等效为 sum(Number*Id) as sum2
                total = b.BarCode.COUNT(),//等效为count(BarCode) as total
                sum11 = b.Number.SUM(),//等效为sum(Number) as sum1
                b.ProductName,
                num1 = b.SUM(x => x.Number * x.Id),
                num2 = b.MAX(x => x.Number * x.Id),
                num3 = b.MIN(x => x.Number * x.Id),
                num4 = b.AVG(x => x.Number * x.Id)
            }).ToList();

            foreach (var item in list4)
            {
                var total = item.total;
            }
            var sql = query.PrintQuery();

            #endregion
        }
        public static void testDistinct()
        {
            #region DISTINCT
            var query = Code.ProductDataRepository.Instance.GetLambdaQuery();
            query.Where(b => b.Id > 0);
            var list5 = query.Select(b => new { dictinct = b.ProductName.DistinctField() }).ToList();
            Console.WriteLine(query.ToString());
            var distinctCount = query.Select(b => new { count = b.ProductName.DistinctCount() }).ToList();
            foreach (var item in list5)
            {
                //var total = item.Total;
                var name = item.dictinct;
            }
            query.PrintQuery();
            #endregion
        }
        #endregion
    }
}
